﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_LABEL_Get_List_For_Ctrl_Binding')
	BEGIN
		DROP Procedure usp_UPDMS_COM_LABEL_Get_List_For_Ctrl_Binding
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_LABEL_Get_List_For_Binding
**	Desc : 콘트롤 바인딩용 라벨정보 조회
**	Test Exec Query : Exec usp_UPDMS_COM_LABEL_Get_List_For_Ctrl_Binding 'ko', 'Button'
**	Called by : Com_Dac_UPDMS_COM_LABEL.cs
**	Program ID : All
**	Auth : 송시명
**	Date : 2011-02-01
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_LABEL_Get_List_For_Ctrl_Binding]
@ls_lang_set nvarchar(2),
@ls_initial nvarchar(100) = ''
AS
SET NOCOUNT ON

IF LEN(@ls_initial) > 1
BEGIN
  IF @ls_initial = 'Weekend' --요일
    SELECT Seq AS [Value],
	       '' AS Initial,
           [Text]
      FROM (
           SELECT Seq,
                  CASE @ls_lang_set WHEN 'ko' THEN Label_Ko +' (' + ISNULL(Label_En,'') + ')'
                                    WHEN 'en' THEN ISNULL(Label_En,'') +' (' + Label_Ko + ')'
                                    WHEN 'ch' THEN ISNULL(Label_Ch,'') +' (' + Label_Ko + ')'
                  END [Text],
                  b.View_Order
             FROM UPDMS_COM_LABEL a WITH(NOLOCK)
             JOIN (
                  SELECT '일' as Day_K_Abb_Nm,0 as View_Order UNION ALL
                  SELECT '월', 1 UNION ALL
                  SELECT '화', 2 UNION ALL
                  SELECT '수', 3 UNION ALL
                  SELECT '목', 4 UNION ALL
                  SELECT '금', 5 UNION ALL
                  SELECT '토', 6
                  ) b
               ON a.Label_Ko = b.Day_K_Abb_Nm
           ) x
     WHERE x.Seq IN (378, 347, 567, 250, 171, 90, 533)
     ORDER BY x.View_Order
  ELSE IF @ls_initial = 'Calendar' --달력
    SELECT Seq AS [Value],
           [Text]
      FROM (
           SELECT Seq,
                  CASE @ls_lang_set WHEN 'ko' THEN Label_Ko +' (' + ISNULL(Label_En,'') + ')'
                                    WHEN 'en' THEN ISNULL(Label_En,'') +' (' + Label_Ko + ')'
                                    WHEN 'ch' THEN ISNULL(Label_Ch,'') +' (' + Label_Ko + ')'
                  END [Text],
                  b.View_Order
             FROM UPDMS_COM_LABEL a WITH(NOLOCK)
             JOIN (
                  SELECT '1월' as Month_K_Nm, 1 as View_Order UNION ALL
                  SELECT '2월', 2 UNION ALL
                  SELECT '3월', 3 UNION ALL
                  SELECT '4월', 4 UNION ALL
                  SELECT '5월', 5 UNION ALL
                  SELECT '6월', 6 UNION ALL
                  SELECT '7월', 7 UNION ALL
                  SELECT '8월', 8 UNION ALL
                  SELECT '9월', 9 UNION ALL
                  SELECT '10월', 10 UNION ALL
                  SELECT '11월', 11 UNION ALL
                  SELECT '12월', 12
                  ) b
               ON a.Label_Ko = b.Month_K_Nm
           ) x
     WHERE x.Seq IN (6, 10, 15, 17, 18, 19, 20, 21, 22, 1, 2, 4)
     ORDER BY x.View_Order
  ELSE IF @ls_initial = 'GridView' --그리드뷰 공통(S, 번호)
    SELECT Seq AS [Value],
           [Text]
      FROM (
           SELECT Seq,
                  CASE @ls_lang_set WHEN 'ko' THEN Label_Ko +' (' + ISNULL(Label_En,'') + ')'
                                    WHEN 'en' THEN ISNULL(Label_En,'') +' (' + Label_Ko + ')'
                                    WHEN 'ch' THEN ISNULL(Label_Ch,'') +' (' + Label_Ko + ')'
                  END [Text]
             FROM UPDMS_COM_LABEL WITH(NOLOCK)
           ) x
     WHERE x.Seq IN (588, 187)
     ORDER BY x.Text
  ELSE IF @ls_initial = 'Button' --버튼
    SELECT Seq AS [Value],
           [Text]
      FROM (
           SELECT Seq,
                  CASE @ls_lang_set WHEN 'ko' THEN Label_Ko +' (' + ISNULL(Label_En,'') + ')'
                                    WHEN 'en' THEN ISNULL(Label_En,'') +' (' + Label_Ko + ')'
                                    WHEN 'ch' THEN ISNULL(Label_Ch,'') +' (' + Label_Ko + ')'
                  END [Text]
             FROM UPDMS_COM_LABEL WITH(NOLOCK)
           ) x
     WHERE x.Seq IN (35,38,124,125,134,157,178,184,189,215,228,236,281,287,292,305,365,374,388,394,414,464,468,476,483,506,570,581,582,600)
     ORDER BY x.Text
END
ELSE
BEGIN
  SELECT Seq AS [Value],
         Initial,
         [Text]
    FROM (
         SELECT Seq,
                [Text],
                CASE WHEN [Text] < 'ㄱ' THEN SUBSTRING([Text], 1, 1)
                     WHEN [Text] < '나' THEN 'ㄱ'
                     WHEN [Text] < '다' THEN 'ㄴ'
                     WHEN [Text] < '라' THEN 'ㄷ'
                     WHEN [Text] < '마' THEN 'ㄹ'
                     WHEN [Text] < '바' THEN 'ㅁ'
                     WHEN [Text] < '사' THEN 'ㅂ'
                     WHEN [Text] < '아' THEN 'ㅅ'
                     WHEN [Text] < '자' THEN 'ㅇ'
                     WHEN [Text] < '차' THEN 'ㅈ'
                     WHEN [Text] < '카' THEN 'ㅊ'
                     WHEN [Text] < '타' THEN 'ㅋ'
                     WHEN [Text] < '파' THEN 'ㅌ'
                     WHEN [Text] < '하' THEN 'ㅍ'
                     WHEN [Text] < 'a'  THEN 'ㅎ'
                     ELSE SUBSTRING([Text], 1, 1)
                 END Initial
           FROM (
                SELECT Seq,
                       CASE @ls_lang_set WHEN 'ko' THEN Label_Ko +' (' + ISNULL(Label_En,'') + ')'
                                         WHEN 'en' THEN ISNULL(Label_En,'') +' (' + Label_Ko + ')'
                                         WHEN 'ch' THEN ISNULL(Label_Ch,'') +' (' + Label_Ko + ')'
                       END [Text]
                  FROM UPDMS_COM_LABEL WITH(NOLOCK)
                ) x
         ) y
   WHERE y.Initial LIKE @ls_initial + '%'
   ORDER BY y.Text

END